package dbLayer;

import modelLayer.Question;

import java.sql.*;
import java.util.ArrayList;

public class QuestionDb {


private Connection con;

public QuestionDb() {
	con = DbConnection.getInstance().getDBcon();
}

//*********Methods***********\\
/*
/**
 * get one PartSalesOrder having the id
 * @param partorderid
 * @return singlewhere
 */
public Question findQuestion(int qNo)
{   String wClause = "  qno = '" + qNo + "'";
    return singleWhere(wClause);
}

public ArrayList<Question> findBySubject(int sNo){
	String wClause = " question.tNo = topic.tNo and topic.lNo = level.lNo and level.sNo = '" + sNo + "'";
	return miscWhere(wClause);
}

public ArrayList<Question> findByLevel(int lNo){
	String wClause = " question.tNo = topic.tNo and topic.lNo = '" + lNo + "'";
	return miscWhere(wClause);
}

public ArrayList<Question> findByTopic(int tNo){
	String wClause = " tNo = '" + tNo + "'";
	return miscWhere(wClause);
}

public ArrayList<Question> findAllQuestions(){
	String wClause = "";
	return miscWhere(wClause);
}

/**
 * singlewhere is used when only one Question object is to be build
 * @param wClause
 * @return QuestionObj
 */
private Question singleWhere(String wClause)
{	
    ResultSet results;
    Question qObj = new Question();
    String query =  buildQuery(wClause);
    System.out.println("QuestionDb -singelWhere " + query); 
    try{ 
    	Statement stmt = con.createStatement();
    	stmt.setQueryTimeout(5);
    	results = stmt.executeQuery(query);
    		if( results.next() ){
    			qObj = buildQuestion(results);
           
    		}//end if
    		stmt.close();
    }//end try	
    catch(Exception e){
    	System.out.println("Query exception - select Question : "+e);
    	e.printStackTrace();
    }
    return qObj;
}

private ArrayList<Question> miscWhere(String wClause){
	ResultSet results;
	ArrayList<Question> list = new ArrayList<Question>();
	
	String query = buildQuery(wClause);
	System.out.println(query);
	try{
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	while(
	 		results.next()){
	 			Question q = new Question();
	 			q = buildQuestion(results);
	 			list.add(q);
	 		}
	}
	catch(Exception e){
		System.out.println("Der er sket en fejl i db");
	}	
	return list;
}

private Question buildQuestion(ResultSet results) {
	 Question qObj = new Question();
	 try{
		 qObj.setqNo(results.getInt("qno"));
		 qObj.setQuestion(results.getString("question"));
		 qObj.setAnswer(results.getString("answer"));
	 }
	 catch(Exception e){
			System.out.println("Der er sket en fejl");

	 }
	return qObj;
}



/**
 * method to build the query
 * @param wClause
 * @return query
 */
	private String buildQuery(String wClause)
	{
	    String query="SELECT qno, question, answer FROM Question";
		if(wClause.contains("lNo"))
			query += ", topic";
		if(wClause.contains("sNo"))
			query += ", level";
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}

	/**
	 * delete partorder with id
	 * @param partorderid
	 * @return rc
	 */
	/*public int delete(int partorderid)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM PartSalesOrder WHERE partorderid = '" +
				partorderid + "'";
                System.out.println(query);
	  	try{ // delete from partsalesorder
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//end try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in partsalesorder db: "+ex);
   	        }
		return(rc);
	}*/

	/**
	 * this query will call a method in SQL that will insert into Question table a tublet with the given attributes 
	 * @param Question 
	 * @return rc
	 * @throws Exception
	 */
	 public int insertQuestion(Question question, int tNo) throws Exception
	    {
	        int nextid = GetMax.getMaxId("Select max(qno) from Question");
	        nextid = nextid + 1;
	        System.out.println("next id = " +  nextid);
	  
	       int rc = -1;
		   String query="INSERT INTO Question(qNo, question, answer, tno)  VALUES('"+
				nextid + "', '" +
				question.getQuestion() + "', '" +  
		   		question.getAnswer() + "', '" + 
				tNo + " ')";
	                     

	       System.out.println("insert : " + query);
	      try{ // insert new partsalesorder
	          Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  rc = stmt.executeUpdate(query);
	          stmt.close();
	      }//end try
	       catch(SQLException ex){
	          System.out.println("Question ikke oprettet");
	          throw new Exception ("Question is not inserted correct");
	       }
	       return(rc);
	    }
	 
	 public void updateQuestion(Question q) throws Exception{
		 String query = "UPDATE Question SET question = '" + q.getQuestion() + 
				 "', answer = '" + q.getAnswer() + 
				 "' WHERE qno = '" + q.getqNo() + "'";
		 	try{
		 		Statement stmt = con.createStatement();
		 		stmt.setQueryTimeout(5);
		 		stmt.executeUpdate(query);
		 		stmt.close();
		 	}
		 	catch(SQLException ex){
		          System.out.println("Question ikke oprettet");
		          throw new Exception ("Question is not inserted correct");
		 	}
	 }
	 
	 public void deleteQuestion(Question q) throws Exception{
		 String query = "DELETE FROM Question where qNo = '" + q.getqNo() + "'";
		 try{
		 		Statement stmt = con.createStatement();
		 		stmt.setQueryTimeout(5);
		 		stmt.executeUpdate(query);
		 		stmt.close();
		 	}
		 	catch(SQLException ex){
		          System.out.println("Question ikke oprettet");
		          throw new Exception ("Question is not inserted correct");
		 	}
	 }
	
}//ends Class

